package com.junzhiit.nengji.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.base.model.BaseModel;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.bo.GoodsCountBO;
import com.junzhiit.nengji.bo.HomeSituationBO;
import com.junzhiit.nengji.bo.HomeTableBO;
import com.junzhiit.nengji.bo.NoticeBO;
import com.junzhiit.nengji.bo.OrdersCountBO;
import com.junzhiit.nengji.bo.OrdersMoneyBO;
import com.junzhiit.nengji.bo.ShopIncomeBO;
import com.junzhiit.nengji.dao.HomeDAO;
import com.junzhiit.nengji.model.Shop;
import com.junzhiit.nengji.model.enums.Is;
import com.junzhiit.nengji.model.enums.NoticeType;
import com.junzhiit.nengji.model.enums.OrderStatus;
import com.junzhiit.nengji.model.enums.Status;

@Repository
public class HomeDAOImpl extends AbstractBaseDAO<BaseModel> implements HomeDAO {
	
	@Override
	public HomeSituationBO getheaderInfo(String shopId){
		StringBuffer sql=new StringBuffer();
		sql.append(" select count(*) total_count,ifnull(sum(money),0.00) total_money, ");
		sql.append(" count(case when (to_days(now())=(to_days(created_time)+1)) then 1 end) today_count,");
		sql.append(" ifnull(sum(case when (to_days(now())=(to_days(created_time)+1)) then money end),0.00) today_money");
		sql.append(" from pay_history where shop_id=? ");
		
		StatementParameter param=new StatementParameter();
		param.setString(shopId);
		
		return super.query(sql.toString(), HomeSituationBO.class, param);
	}
	
	@Override
	public List<OrdersMoneyBO> queryOrdersMoney(String shopId,int year,int month){
		StringBuffer sql=new StringBuffer();
		sql.append(" select ");
		sql.append(" day(created_time) day,sum(money) money");
		sql.append(" from pay_history ");
		sql.append(" where");
		sql.append(" month(created_time)=?");
		sql.append(" and year(created_time)=?");
		sql.append(" and shop_id=? ");
		sql.append(" group by day(created_time) ");
		
		StatementParameter param=new StatementParameter();
		param.setInt(month);
		param.setInt(year);
		param.setString(shopId);
		
		return super.queryForList(sql.toString(),OrdersMoneyBO.class,param);
	}
	
	@Override
	public List<OrdersCountBO> queryOrdersCount(String shopId,int year,int month){
		StringBuffer sql=new StringBuffer();
		sql.append(" select ");
		sql.append(" day(created_time) day,count(*) count");
		sql.append(" from orders ");
		sql.append(" where ");
		sql.append(" month(created_time)=?");
		sql.append(" and year(created_time)=?");
		sql.append(" and shop_id=? ");
		sql.append(" group by day(created_time) ");
		
		StatementParameter param=new StatementParameter();
		param.setInt(month);
		param.setInt(year);
		param.setString(shopId);
		
		return super.queryForList(sql.toString(), OrdersCountBO.class,param);
	}
	
	
	@Override
	public List<GoodsCountBO> queryForGoods(String shopId,int month){
		StringBuffer sql=new StringBuffer();
		sql.append(" select");
		sql.append(" g.id,g.`name`,sum(og.number) total_count");
		sql.append(" from shop s");
		sql.append(" inner join orders o on s.id=o.shop_id");
		sql.append(" inner join orders_goods og on og.order_id=o.id");
		sql.append(" inner join goods g on g.id=og.goods_id");
		sql.append(" where");
		sql.append(" month(o.created_time)=?");
		sql.append(" and s.id=?");
		sql.append(" and o.status<>? and o.status<>?");
		sql.append(" group by g.id ");
		sql.append(" order by sum(og.number) desc");
		sql.append(" limit 0,10");
		
		StatementParameter param=new StatementParameter();
		param.setInt(month);
		param.setString(shopId);
		param.setInt(OrderStatus.WAIT.toIntValue());
		param.setInt(OrderStatus.CANCEL.toIntValue());
		
		return queryForList(sql.toString(), GoodsCountBO.class, param);
	}
	
	@Override
	public List<OrdersMoneyBO> queryAllOrdersMoney(int year,int month) {
		StringBuffer sql=new StringBuffer();
		sql.append(" select ");
		sql.append(" day(created_time) day,sum(money) money");
		sql.append(" from pay_history ");
		sql.append(" where");
		sql.append(" month(created_time)=?");
		sql.append(" and year(created_time)=?");
		sql.append(" and is_delete = ?");
		sql.append(" group by day(created_time) ");
		
		StatementParameter param=new StatementParameter();
		param.setInt(month);
		param.setInt(year);
		param.setInt(IsDelete.NO.toIntValue());
		
		return super.queryForList(sql.toString(),OrdersMoneyBO.class,param);
	}

	@Override
	public List<OrdersCountBO> queryAllOrdersCount(int year,int month) {
		StringBuffer sql=new StringBuffer();
		sql.append(" select ");
		sql.append(" day(created_time) day,count(*) count");
		sql.append(" from orders ");
		sql.append(" where ");
		sql.append(" month(created_time)=?");
		sql.append(" and year(created_time)=?");
		sql.append(" and is_delete = ?");
		sql.append(" group by day(created_time) ");
		
		StatementParameter param=new StatementParameter();
		param.setInt(month);
		param.setInt(year);
		param.setInt(IsDelete.NO.toIntValue());
		
		return super.queryForList(sql.toString(), OrdersCountBO.class,param);
	}

	
	@Override
	public List<NoticeBO> queryInternalNotice(){
		StringBuffer sql=new StringBuffer();
		sql.append("select n.*,s.name 'shop_name',su.name 'issuer_name',su.mobile_phone from `notice` n");
		sql.append(" left join `shop` s on n.shop_id=s.id");
		sql.append(" left join system_user su on su.id=n.issuer");
		sql.append(" where n.is_delete=? and n.status=? and n.type = ?");
		sql.append(" order by n.sort desc,n.created_time desc");
		sql.append(" limit 0,5");
		
		StatementParameter param=new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Status.ACTIVE.toIntStatus());
		param.setInt(NoticeType.BASE.toIntValue());
		return queryForList(sql.toString(), NoticeBO.class,param);
	}

	@Override
	public List<NoticeBO> queryInternalShopNotice() {
		StringBuffer sql=new StringBuffer();
		sql.append("select n.*,s.name 'shop_name',su.name 'issuer_name',su.mobile_phone from `notice` n");
		sql.append(" left join `shop` s on n.shop_id=s.id");
		sql.append(" left join system_user su on su.id=n.issuer");
		sql.append(" where n.is_delete=? and n.status=? and n.is_shop=? and n.type<>?");
		sql.append(" order by n.sort desc,n.created_time desc");
		sql.append(" limit 0,5");
		
		StatementParameter param=new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Status.ACTIVE.toIntStatus());
		param.setInt(Is.YES.toIntValue());
		param.setInt(NoticeType.SHOP.toIntValue());
		return queryForList(sql.toString(), NoticeBO.class,param);
	}

	@Override
	public List<GoodsCountBO> querySsoGoods(HomeTableBO homeTableBO) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		sql.append(" select");
		sql.append(" g.id,g.`name`,sum(og.number) total_count");
		sql.append(" from shop s");
		sql.append(" inner join orders o on s.id=o.shop_id");
		sql.append(" inner join chain c on s.chain_id = c.id");
		sql.append(" inner join orders_goods og on og.order_id=o.id");
		sql.append(" inner join goods g on g.id=og.goods_id");
		sql.append(" where c.id=?");
		sql.append(" and year(o.created_time)=?");
		sql.append(" and month(o.created_time)=?");
		sql.append(" and o.status<>? and o.status<>?");
		if(StringUtils.isNotBlank(homeTableBO.getShopId())){
			sql.append(" and o.shop_id = ?");
			param.setString(homeTableBO.getShopId());
		}
		sql.append(" group by g.id ");
		sql.append(" order by sum(og.number) desc");
		sql.append(" limit 0,10");
		
		param.setString(homeTableBO.getChainId());
		param.setInt(homeTableBO.getYear());
		param.setInt(homeTableBO.getMonth());
		param.setInt(OrderStatus.WAIT.toIntValue());
		param.setInt(OrderStatus.CANCEL.toIntValue());
		
		return queryForList(sql.toString(), GoodsCountBO.class, param);
	}

	@Override
	public List<ShopIncomeBO> queryShopIncome(HomeTableBO homeTableBO) {
		StringBuffer sql=new StringBuffer();
		sql.append("select");
		sql.append(" s.name,sum(ph.money) value");
		sql.append(" from pay_history ph");
		sql.append(" inner join shop s on s.id=ph.shop_id");
		sql.append(" inner join chain c on s.chain_id = c.id");
		sql.append(" where c.id=?");
		sql.append(" and year(ph.created_time)=?");
		sql.append(" and month(ph.created_time)=?");
		sql.append(" group by s.id ");
		sql.append(" order by sum(ph.money) desc");
		
		StatementParameter param=new StatementParameter();
		param.setString(homeTableBO.getChainId());
		param.setInt(homeTableBO.getYear());
		param.setInt(homeTableBO.getMonth());
		
		return queryForList(sql.toString(), ShopIncomeBO.class, param);
	}

	@Override
	public List<Shop> queryAllShop(String chainId) {
		StringBuffer sql=new StringBuffer();
		sql.append("select * from shop where chain_id = ? and is_delete = ?");
		
		StatementParameter param=new StatementParameter();
		param.setString(chainId);
		param.setInt(Is.NO.toIntValue());
		
		return queryForList(sql.toString(), Shop.class, param);
	}
}
